package com.weic.spark.scala.hw

import org.apache.spark.sql.SparkSession

/**
 * @Auther:BigData-weic
 * @ClassName:_02SparkSqlWork
 * @Date:2020/12/14 20:15
 * @功能描述: spqrkSQL查询
 * @Version:1.0
 */
object _02SparkSqlWork {
	def main(args: Array[String]): Unit = {
		val spark = SparkSession.builder()
    		.appName("_02SparkSqlWork")
    		.master("local[*]")
    		.getOrCreate()
		//读取数据
		val df = spark.read.json("file:\\F:\\datas\\work12_14\\employee.json")
		//创建一张临时视图
		df.createOrReplaceTempView("person")
		println("---------查询全部字段去重---------")
		var sql =
			"""
			  |select distinct
			  |  id,age,name
			  |from person
			  |""".stripMargin
		spark.sql(sql).show()
		println("--------查询时不要ID字段------")
		sql =
			"""
			  |select
			  |  age,name
			  |from person
			  |""".stripMargin
		spark.sql(sql).show()
		println("--------筛选出age>30的记录-------")
		sql =
			"""
			  |select
			  |  id,age,name
			  |from person
			  |where age>'30'
			  |""".stripMargin
		spark.sql(sql).show()
		println("--------查询所有记录的name列，并为其取名为username-------")
		import spark.implicits._
		df.select($"name".as("username"))
			.show()
		println("--------查询年龄age的平均值-------")
		sql =
			"""
			  |select
			  |  avg(age) `ageAvg`
			  |from person
			  |""".stripMargin
		spark.sql(sql).show()
		//释放资源
		spark.stop()
	}
}
